﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace LoverMeeting.DataBase.Core
{
    public class SqlServerDao
    {
        private readonly string _connectionString;
        private SqlConnection _sharedConnection;
        public SqlConnection SharedConnection
        {
            get {
                return _sharedConnection ??
                       (_sharedConnection =
                        new SqlConnection(_connectionString));
            }
            set
            {
                _sharedConnection = value;
            }
        }

        public SqlServerDao(SqlConnection connection)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            _sharedConnection = connection;
        }

        public SqlServerDao(string connectionString)
        {
            if (string.IsNullOrEmpty(connectionString))
                throw new ArgumentNullException("connectionString");
            _connectionString = connectionString;
        }

        public SqlCommand GetSqlCommand(string sqlQuery)
        {
            var command = new SqlCommand
                              {
                                  Connection = SharedConnection, 
                                  CommandType = CommandType.Text, 
                                  CommandText = sqlQuery
                              };
            return command;
        }

        public SqlCommand GetStoredProcedureCommand(string storedProcedureName)
        {
            var command = new SqlCommand(storedProcedureName)
                              {
                                  Connection = SharedConnection,
                                  CommandType = CommandType.StoredProcedure
                              };
            return command;
        }

        public SqlParameter CreateNullParameter(string name, SqlDbType paramType)
        {
            var parameter = new SqlParameter
                                {
                                    SqlDbType = paramType,
                                    ParameterName = name,
                                    Value = null,
                                    Direction = ParameterDirection.Input
                                };
            return parameter;
        }

        public SqlParameter CreateNullParameter(string name, SqlDbType paramType, int size)
        {
            var parameter = new SqlParameter
                                {
                                    SqlDbType = paramType,
                                    ParameterName = name,
                                    Size = size,
                                    Value = null,
                                    Direction = ParameterDirection.Input
                                };
            return parameter;
        }

        public SqlParameter CreateOutputParameter(string name, SqlDbType paramType)
        {
            var parameter = new SqlParameter
                                {
                                    SqlDbType = paramType, 
                                    ParameterName = name, 
                                    Direction = ParameterDirection.Output
                                };
            return parameter;
        }

        public SqlParameter CreateOutputParameter(string name, SqlDbType paramType, int size)
        {
            var parameter = new SqlParameter
                                {
                                    SqlDbType = paramType,
                                    Size = size,
                                    ParameterName = name,
                                    Direction = ParameterDirection.Output
                                };
            return parameter;
        }

        public SqlParameter CreateParameter(string name, int? value)
        {
            if (value == null)
            {
                return CreateNullParameter(name, SqlDbType.Decimal);
            }
            var parameter = new SqlParameter
                                {
                                    SqlDbType = SqlDbType.Decimal,
                                    ParameterName = name,
                                    Value = value.Value,
                                    Direction = ParameterDirection.Input
                                };
            return parameter;
        }

        public SqlParameter CreateParameter(string name, DateTime? value)
        {
            if (!value.HasValue)
            {
                return CreateNullParameter(name, SqlDbType.Date);
            }
            var parameter = new SqlParameter
                                {
                                    SqlDbType = SqlDbType.Date,
                                    ParameterName = name,
                                    Value = value.Value.Date,
                                    Direction = ParameterDirection.Input
                                };
            return parameter;
        }

        public SqlParameter CreateParameter(string name, decimal? value, int size)
        {
            if (!value.HasValue)
            {
                return CreateNullParameter(name, SqlDbType.Decimal);
            }
            var parameter = new SqlParameter
            {
                SqlDbType = SqlDbType.Decimal,
                Size = size,
                ParameterName = name,
                Value = value.Value,
                Direction = ParameterDirection.Input
            };
            return parameter;
        }


        public SqlParameter CreateParameter(string name, string value, int size)
        {
            if (String.IsNullOrEmpty(value))
            {
                return CreateNullParameter(name, SqlDbType.VarChar);
            }
            var parameter = new SqlParameter
                                {
                                    SqlDbType = SqlDbType.VarChar,
                                    Size = size,
                                    ParameterName = name,
                                    Value = value,
                                    Direction = ParameterDirection.Input
                                };
            return parameter;
        }

        public SqlParameter CreateParameter(string name, char value)
        {
            if (value == '\0')
            {
                return CreateNullParameter(name, SqlDbType.VarChar);
            }
            var parameter = new SqlParameter
            {
                SqlDbType = SqlDbType.VarChar,
                Size = 1,
                ParameterName = name,
                Value = value,
                Direction = ParameterDirection.Input
            };
            return parameter;
        }

        public void ExecuteNonQuery(SqlCommand command)
        {
            if (command == null) return;
            try
            {
                
                command.Connection = SharedConnection;
                if (command.Connection != null && command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                command.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                Console.WriteLine("SqlException {0} occured", e.Message);
                if (e.Number == 24381)
                    for (int i = 0; i < e.Errors.Count; i++)
                        Console.WriteLine("Array Bind Error {0} occured at Row Number {1}",
                          e.Errors[i].Message, e.Errors[i].LineNumber);
                throw;
            }
            catch (Exception e)
            {
                throw new Exception("Error executing query", e);
            }
            finally
            {
                if (command.Connection != null) command.Connection.Close();
            }
        }

        public Object ExecuteScalar(SqlCommand command)
        {
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                return command.ExecuteScalar();
            }
            catch (Exception e)
            {
                throw new Exception("Error executing query", e);
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public T GetSingleValue<T>(SqlCommand command)
        {
            T returnValue = default(T);
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                var reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    if (!reader.IsDBNull(0)) { returnValue = (T)reader[0]; }
                    reader.Close();
                }
            }
            catch (Exception e)
            {
                throw new Exception("Error populating data", e);
            }
            finally
            {
                command.Connection.Close();
            }
            return returnValue;
        }

        public Int32 GetSingleInt32(SqlCommand command)
        {
            var returnValue = default(int);
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                var reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    if (!reader.IsDBNull(0)) { returnValue = reader.GetInt32(0); }
                    reader.Close();
                }
            }
            catch (Exception e)
            {
                throw new Exception("Error populating data", e);
            }
            finally
            {
                command.Connection.Close();
            }
            return returnValue;
        }

        public string GetSingleString(SqlCommand command)
        {
            string returnValue = null;
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                var reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    if (!reader.IsDBNull(0)) { returnValue = reader.GetString(0); }
                    reader.Close();
                }
            }
            catch (Exception e)
            {
                throw new Exception("Error populating data", e);
            }
            finally
            {
                command.Connection.Close();
            }
            return returnValue;
        }

        public List<string> GetStringList(SqlCommand command)
        {
            List<string> returnList = null;
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                var reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    returnList = new List<string>();
                    while (reader.Read())
                    {
                        if (!reader.IsDBNull(0))
                        {
                            returnList.Add(reader.GetString(0));
                        }
                    }
                    reader.Close();
                }
            }
            catch (Exception e)
            {
                throw new Exception("Error populating data", e);
            }
            finally
            {
                command.Connection.Close();
            }
            return returnList;
        }
    }
}
